from data_models import SQLTableRow

class EmployeeDB_DDLs:
    employees = """
    CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id INT NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    manager_id INT,
    department_id INT,
    FOREIGN KEY (job_id) REFERENCES jobs(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);
"""
    departments = """
    CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location_id INT
);
"""
    jobs = """
    CREATE TABLE jobs (
    id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    min_salary DECIMAL(10, 2),
    max_salary DECIMAL(10, 2)
);
"""
    dependents = """
    CREATE TABLE dependents (
    id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    relationship VARCHAR(50) NOT NULL,
    employee_id INT NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);
"""

employees_table = SQLTableRow(
    server="PROD_SERVER_1",
    database="HR_DATABASE",
    schema="human_resources",
    table_name="employees",
    table_ddl=EmployeeDB_DDLs.employees
)

departments_table = SQLTableRow(
    server="PROD_SERVER_1",
    database="HR_DATABASE",
    schema="human_resources",
    table_name="departments",
    table_ddl=EmployeeDB_DDLs.departments
)

jobs_table = SQLTableRow(
    server="PROD_SERVER_1",
    database="HR_DATABASE",
    schema="human_resources",
    table_name="jobs",
    table_ddl=EmployeeDB_DDLs.jobs
)

dependents_table = SQLTableRow(
    server="PROD_SERVER_1",
    database="HR_DATABASE",
    schema="human_resources",
    table_name="dependents",
    table_ddl=EmployeeDB_DDLs.dependents
)